package cn.itcast.bos.action.base;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.convention.annotation.Action;
import org.apache.struts2.convention.annotation.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;

import cn.itcast.bos.domain.base.Area;
import cn.itcast.bos.service.base.AreaService;
import cn.itcast.bos.utils.BaseAction;
import cn.itcast.bos.utils.PinYin4jUtils;

public class AreaAction extends BaseAction<Area> {

	// 业务层对象注入
	@Autowired
	private AreaService areaService;

	// 属性注入文件
	private File file;
	
	public void setFile(File file) {
		this.file = file;
	}

	// 属性注入文件名
	private String fileFileName;

	public void setFileFileName(String fileFileName) {
		this.fileFileName = fileFileName;
	}
	
	//批量导入区域excel表
	@Action(value = "area_batchImport")
	public String batchImport() throws FileNotFoundException, IOException {
		Set<Area> set = new HashSet<Area>();
		if (fileFileName.endsWith("xls")) {
			// 加载xls文件
			HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(new FileInputStream(file));
			// 获取第一个工作簿
			HSSFSheet sheet = hSSFWorkbook.getSheetAt(0);
			// 遍历工作簿的每一行
			for (Row row : sheet) {
				batchImportSet(set,row);
			}
		} else {
			// 加载xlsx文件
			XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(new FileInputStream(file));
			// 获取第一个工作簿
			XSSFSheet sheet = xSSFWorkbook.getSheetAt(0);
			// 遍历工作簿的每一行
			for (Row row : sheet) {
				batchImportSet(set,row);
			}
		}
		areaService.batchImport(set);
		return NONE;
	}
	public void batchImportSet(Set<Area> set,Row row) {
		if (row.getRowNum() == 0) {
			// 如果是第一行,返回
			return;
		}
		if (row.getCell(0) == null || StringUtils.isBlank(row.getCell(0).getStringCellValue())) {
			// 如果是空白行，返回
			return;
		}
		Area area = new Area();
		area.setId(row.getCell(0).getStringCellValue());
		area.setProvince(row.getCell(1).getStringCellValue());
		area.setCity(row.getCell(2).getStringCellValue());
		area.setDistrict(row.getCell(3).getStringCellValue());
		area.setPostcode(row.getCell(4).getStringCellValue());
		
		//基于pinyin4j设置城市编码和简码
		String province = area.getProvince();
		String city = area.getCity();
		String district = area.getDistrict();
		
		province = province.substring(0, province.length()-1);
		city = city.substring(0, city.length()-1);
		district = district.substring(0, district.length()-1);
		//获取每个汉字的拼音大写首字母
		String[] strings = PinYin4jUtils.getHeadByString(province+city+district);
		StringBuffer stringBuffer = new StringBuffer();
		for (String string : strings) {
			stringBuffer.append(string);
		}
		//设置城市简码
		area.setShortcode(stringBuffer.toString());
		//设置城市编码
		String cityCode = PinYin4jUtils.hanziToPinyin(city, "");
		area.setCitycode(cityCode);
		set.add(area);
	}
	//分页查询区域数据
	@Action(value="area_pageQuery",results={@Result(name="success",type="json")})
	public String pageQuery(){
		//创建分页查询对象
		Pageable pageable =  new PageRequest(page-1,rows);
		//创建条件查询对象
		Specification<Area> specification = new Specification<Area>() {

			@Override
			public Predicate toPredicate(Root<Area> root, CriteriaQuery<?> query,
					CriteriaBuilder cb) {
//				创建保存条件的集合对象
				Set<Predicate> set = new HashSet<Predicate>();
				if(StringUtils.isNotBlank(model.getProvince())){
					Predicate p1 = cb.like(root.get("province").as(String.class),"%"+ model.getProvince()+"%");
					set.add(p1);
				}
				if(StringUtils.isNotBlank(model.getCity())){
					Predicate p2 = cb.like(root.get("city").as(String.class),"%"+ model.getCity()+"%");
					set.add(p2);
				}
				if(StringUtils.isNotBlank(model.getDistrict())){
					Predicate p3 = cb.like(root.get("district").as(String.class),"%"+ model.getDistrict()+"%");
					set.add(p3);
				}
				return cb.and(set.toArray(new Predicate[0]));
			}
		};
		
//		调用业务层分页查询
		Page<Area> pageData = areaService.pageQuery(specification,pageable);
		//把分页查询到的数据压入到值栈
		pushPageDataToValueStack(pageData);
		return SUCCESS;
	}
	@SuppressWarnings("resource")
	@Action(value="area_batchOutput")
	public String batchOutput() throws IOException{
		//1.創建工作簿和sheet
		XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
		XSSFSheet sheet = xssfWorkbook.createSheet();
		//2.设置列宽度
		sheet.setDefaultColumnWidth(100);
		//3.设置表头
		XSSFRow createRow = sheet.createRow(0);
		createRow.createCell(0).setCellValue("省");
		createRow.createCell(1).setCellValue("市");
		createRow.createCell(2).setCellValue("区");
		createRow.createCell(3).setCellValue("邮编");
		createRow.createCell(4).setCellValue("简码");
		createRow.createCell(5).setCellValue("城市编码");
		//4、查询数据写入到表格中
		Set<Area> areas = areaService.findAll();
		int rowNum=1;
		for (Area area : areas) {
			XSSFRow row = sheet.createRow(rowNum++);
			row.createCell(0).setCellValue(area.getProvince());
			row.createCell(1).setCellValue(area.getCity());
			row.createCell(2).setCellValue(area.getDistrict());
			row.createCell(3).setCellValue(area.getPostcode());
			row.createCell(4).setCellValue(area.getShortcode());
			row.createCell(5).setCellValue(area.getCitycode());
		}
		HttpServletResponse response = ServletActionContext.getResponse();
		
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
		response.setHeader("Content-Disposition", "attachment;filename=areas.xlsx");
		ServletOutputStream os = response.getOutputStream();
		xssfWorkbook.write(os);
		os.flush();
		return NONE;
	}
}
